if exists(select * from sys.databases db where db.name = 'ShopingClient')
use master
drop database ShopingClient
go
create database ShopingClient
go
use ShopingClient
go
create table users(
	id int identity(1,1),
	username nvarchar(30),
	password nvarchar(30),
	fullname nvarchar(30),
	age int,
	primary key(id)
)
go
create table products(
	id int identity(1,1),
	ProductName nvarchar(40),
	ProductImage nvarchar(50),
	Price int,
	primary key(id)
)
go
create table orders(
	OrderCode nvarchar(20),
	UserId int,
	AccBank nvarchar(30),
	OrderDate datetime,
	TotalCost int,
	primary key (OrderCode),
	foreign key (UserId) references users(id)
)
go
create table orderdetail(
	id int identity(1,1),
	OrderCode nvarchar(20),
	ProductId int,
	Quantity int,
	primary key (id),
	foreign key (OrderCode) references orders(OrderCode),
	foreign key (ProductId) references products(id)
)
go
select * from orderDetail
go
----
create proc InsertUser
	@username nvarchar(30),
	@password nvarchar(30),
	@fullname nvarchar(30),
	@age int
as
	insert into users values(@username,HashBytes('MD5',@password),@fullname,@age)
go
exec InsertUser 'admin','123456','havn' ,21
go
create proc CheckLogin
	@username nvarchar(30),
	@password nvarchar(30)
as
	select * from users where username = @username and password = HashBytes('MD5',@password)
go
insert into products values ('nokiaE71','images/E71.jpg',1000)
insert into products values ('IPad 2','images/ipad2.png',2000)
insert into products values ('IPhone 4','images/iphone4.jpg',1200)
insert into products values ('HTC HD2','images/htc-hd2.jpg',1300)
insert into products values ('Blackberry Bold 9900','images/blackberry-bold-9900-Unbranded.jpg',1500)
insert into products values ('Nokia N900','images/Nokia-N900.jpg',1500)

--delete products
go
create proc ListProduct
as
	select * from products
go
exec ListProduct
go
create proc InsertOrder
	@OrderCode nvarchar(20),
	@UserId int,
	@AccBank nvarchar(30),
	@TotalCost int
as
	insert into orders values (@OrderCode,@UserId,@AccBank,getdate(),@TotalCost)
go
create proc InsertDetail
	@OrderCode nvarchar(20),
	@ProductId int,
	@Quantity int
as
	insert into orderdetail values(@OrderCode,@ProductId,@Quantity)
go
